--TAO TABLE DEAN
IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='DEAN')
BEGIN 
	DROP TABLE DEAN
END
GO 
CREATE TABLE DEAN
(
	TENDA NVARCHAR(30),
	MADA  INT,
	DDIEM_DA NVARCHAR(30),
	PHONG	INT
	PRIMARY KEY (MADA)
)


--TAO TABLE DIADIEM_PHG

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='DIADIEM_PHG')
BEGIN 
	DROP TABLE DIADIEM_PHG
END
GO 
CREATE TABLE DIADIEM_PHG
(
	MAPHG INT,
	DIADIEM NVARCHAR(30)
	PRIMARY KEY (MAPHG,DIADIEM)
)


--TAO TABLE NHANVIEN

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='NHANVIEN')
BEGIN 
	DROP TABLE NHANVIEN
END
GO 
CREATE TABLE NHANVIEN
(
	HONV NVARCHAR(10),
	TENLOT NVARCHAR(20),
	TENNV NVARCHAR(10),
	MANV VARCHAR(10),
	NGSINH SMALLDATETIME,
	DCHI NVARCHAR(100),
	PHAI NVARCHAR(3),
	LUONG NUMERIC,
	MA_NQL VARCHAR(10),
	PHG INT
	PRIMARY KEY(MANV)
)


--TAO TABLE PHONGBAN

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='PHONGBAN')
BEGIN 
	DROP TABLE PHONGBAN
END
GO 
CREATE TABLE PHONGBAN
(
	TENPHG NVARCHAR(10),
	MAPHG INT,
	TRPHG VARCHAR(10),
	NG_NHANCHUC SMALLDATETIME
	PRIMARY KEY(MAPHG)
)


--TAO TABLE PHANCONG

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='PHANCONG')
BEGIN 
	DROP TABLE PHANCONG
END
GO 
CREATE TABLE PHANCONG
(
	MA_NVIEN VARCHAR(10),
	SODA INT,
	THOIGIAN FLOAT
		PRIMARY KEY (MA_NVIEN,SODA)
)


--TAO TABLE THANNHAN 

IF EXISTS (SELECT * FROM DBO.SYSOBJECTS WHERE NAME='THANNHAN')
BEGIN 
	DROP TABLE THANNHAN
END
GO 
CREATE TABLE THANNHAN
(
	MA_NVIEN VARCHAR(10),
	TENTN NVARCHAR(30),
	PHAI VARCHAR(10),
	NGSINH DATETIME,
	QUANHE VARCHAR(30)

	PRIMARY KEY(MA_NVIEN,TENTN)
)


--TAO TABLE THANHVIEN
CREATE TABLE THANHVIEN
(
	ID		INT IDENTITY(0,1),
	TENDN	NVARCHAR(30),
	MATKHAU	NVARCHAR(30),
	VAITRO	INT,
	GHICHU	NVARCHAR(100)

	PRIMARY KEY(ID)
)
